#百度通用翻译API,不包含词典、tts语音合成等资源，如有相关需求请联系translate_api@baidu.com
# coding=utf-8

import http.client
import hashlib
import urllib
import random
import json
import xlrd
import xlwt
from xlutils.copy import copy

def traslate(text: str) -> str:
    appid = '20210121000676996'  # 填写你的appid
    secretKey = 'ky4NrJmq1qQ1YdGR1GYt'  # 填写你的密钥

    httpClient = None
    myurl = '/api/trans/vip/translate'

    fromLang = 'auto'   #原文语种
    toLang = 'zh'   #译文语种
    salt = random.randint(32768, 65536)

    q = text
    sign = appid + q + str(salt) + secretKey
    sign = hashlib.md5(sign.encode()).hexdigest()
    myurl = myurl + '?appid=' + appid + '&q=' + urllib.parse.quote(q) + '&from=' + fromLang + '&to=' + toLang + '&salt=' + str(
    salt) + '&sign=' + sign

    result = ""
    try:
        httpClient = http.client.HTTPConnection('api.fanyi.baidu.com')
        httpClient.request('GET', myurl)

        # response是HTTPResponse对象
        response = httpClient.getresponse()
        result_all = response.read().decode("utf-8")
        result_json = json.loads(result_all)
        result = result_json["trans_result"][0]["dst"]    
    except Exception as e:
        print (e)
    finally:
        if httpClient:
            httpClient.close()
    return result
        
def readExcel(workbook, sheetIndex: int, row: int, col: int) -> str:
    sheet = workbook.sheet_by_index(sheetIndex)#通过索引获取表格\
    result = sheet.cell_value(row, col)
    return result

def writeExcel(writeBook, sheetIndex: int, row: int, col: int, value: str):
    sheet = writeBook.get_sheet(sheetIndex)
    sheet.write(row, col, value)
    return


readWB = xlrd.open_workbook("./reviews.xls")
writeWB = copy(readWB)
# sheet 0 android
sheetIndex = 0
worksheet = readWB.sheet_by_index(sheetIndex)
numberOfRows = worksheet.nrows
for row in range(1, numberOfRows):
    text = readExcel(readWB, sheetIndex, row, 9)
    if text:   
        translatedText = traslate(text)
        print(f"翻译第{row}行：{text}\n 结果：{translatedText}")
        writeExcel(writeWB, sheetIndex, row, 8, translatedText)     
writeWB.save("./reviews_copy.xls")